import traceback
import datetime
import pymysql
import json
import requests
import time
from config import *
import sys


def get_conn():
    """
    :return: 连接，游标
    """
    # 创建连接
    conn = pymysql.connect(host=HOST,
                           user=USER,
                           password=PASSWORD,
                           db=DATABASE,
                           charset="utf8")
    # 创建游标
    cursor = conn.cursor()  # 执行完毕返回的结果集默认以元组显示
    return conn, cursor


def close_conn(conn, cursor):
    cursor.close()
    conn.close()


def query(sql, *args):
    """
    封装通用查询
    :param sql:
    :param args:
    :return: 返回查询到的结果，((),(),)的形式
    """
    conn, cursor = get_conn()
    cursor.execute(sql, args)
    res = cursor.fetchall()
    close_conn(conn, cursor)
    return res


def get_address():
    sql = """
    select id,province,city,county,address from risk_area
    where end_update_time = (select end_update_time from risk_area 
    order by id desc limit 1)   
    """
    res = query(sql)
    return res


def gd_map(addr):
    url = 'https://restapi.amap.com/v3/geocode/geo'  # 高德地图地理编码API服务地址
    params = {'key': '你申请的高德web Key',  # 高德Key
              'address': addr}  # 地址参数
    res = requests.get(url, params)
    print(addr)
    print(res)
    jd = json.loads(res.text)['geocodes'][0]['location']
    print(jd)
    return jd


def get_location():
    data = get_address()
    loctions = []
    for itme in data:
        address = itme[1] + itme[2] + itme[3] + itme[4]
        loction = gd_map(address)
        loctions.append([loction, itme[0]])
    return loctions


def update_risk_location():
    cursor = None
    conn = None
    try:
        conn, cursor = get_conn()
        sql = "UPDATE risk_area SET location = %s where id = %s"
        sql_query = 'select %s=(select end_update_time from risk_area order by id desc limit 1)'

        today = datetime.date.today()
        cursor.execute(sql_query, today)
        request = cursor.fetchone()[0]
        print(request)
        if request == 1:
            locations = get_location()
            print(f"{time.asctime()}中高风险区域数据存在，开始获得经纬度")
            for item in locations:
                cursor.execute(sql, item)
            conn.commit()  # 提交事务 update delete insert操作
            print(f"{time.asctime()}更新最新数据完毕")
        else:
            print(f"{time.asctime()}中高风险区域数据不存在，无法获得经纬度")
    except:
        traceback.print_exc()
    finally:
        print(f"{time.asctime()}结束加载")
        close_conn(conn, cursor)


if __name__ == "__main__":
    update_risk_location()

    # linux下的部署
    # s = """请输入参数:
    # up_risk_location  更新中高风险地区坐标
    # """
    # l = len(sys.argv)
    # if l == 1:
    #     print(s)
    # else:
    #     order = sys.argv[1]
    #     if order == "up_risk_location":
    #         update_risk_location()
    #     else:
    #         print("错误的参数")
    #         print(s)
